package com.treasure.mall.biz.service.core.statistics.impl;

import com.treasure.mall.biz.base.AbstractSearchableCrudService;
import com.treasure.mall.biz.base.SpecificationAndSort;
import com.treasure.mall.biz.base.UserDetails;
import com.treasure.mall.biz.dto.BaseSaveDTO;
import com.treasure.mall.biz.dto.BaseSearchDTO;
import com.treasure.mall.biz.dto.order.OrderStatisticsDTO;
import com.treasure.mall.biz.dto.order.UserOrderCountDTO;
import com.treasure.mall.biz.entity.statistics.OrderStatisticsPO;
import com.treasure.mall.biz.entity.statistics.OrderStatisticsPO_;
import com.treasure.mall.biz.repository.statistics.OrderStatisticsRepository;
import com.treasure.mall.biz.service.core.statistics.OrderStatisticsService;
import com.treasure.mall.zbq.common.Jsr310Utils;
import com.treasure.mall.zbq.common.SqlDataReader;
import com.treasure.mall.zbq.common.ienum.auth.RoleEnum;
import com.treasure.mall.zbq.common.ienum.order.OrderEnums;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.Predicate;
import java.math.BigInteger;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * @author allan
 * @version 1.0.0
 * @date 2020/6/19
 */
@Service
public class OrderStatisticsServiceImpl extends AbstractSearchableCrudService<OrderStatisticsPO, Long> implements OrderStatisticsService {
    private OrderStatisticsRepository orderStatisticsRepository;

    @Autowired
    protected OrderStatisticsServiceImpl(OrderStatisticsRepository repository) {
        super(repository);
        this.orderStatisticsRepository = repository;
    }

    @Override
    public OrderStatisticsDTO orderStatistics(UserDetails userDetails) {
        String sqlWhere = "";
        if (userDetails.getRoleType() == RoleEnum.MERCHANT) {
            sqlWhere += " and sub.rel_merchant_id=" + userDetails.getId();
        }

        LocalDateTime end = Jsr310Utils.DateTime.lastOfDay(LocalDateTime.now());
        LocalDateTime begin = Jsr310Utils.DateTime.startOfDay(end.minusDays(1));
        String beginTime = Jsr310Utils.DateTime.asString(begin);
        String endTime = Jsr310Utils.DateTime.asString(end);
        OrderStatisticsDTO statisticsDTO = new OrderStatisticsDTO();

        //今日，昨日数据
        String sql = "select ifnull(count(1), 0) as totalNum, ifnull(sum(sub.final_amount), 0) as totalAmount, DATE(sub.create_time) as createTime " +
                "from zb_sub_order sub" +
                " LEFT JOIN zb_order o on o.id=sub.rel_order_id " +
                " where sub.create_time >= '" + beginTime + "' " +
                "  and sub.create_time <= '" + endTime + "' " + sqlWhere +
                " and o.pay_status = 1 group by DATE(sub.create_time) ";

        List<SqlDataReader> readers = executeReaderList(sql);
        for (SqlDataReader reader : readers) {
            if (beginTime.contains(reader.getString("createTime"))) {
                //昨日的
                statisticsDTO.setTotalAmountY(reader.getBigDecimal("totalAmount"));
                statisticsDTO.setTotalNumY(reader.getInteger("totalNum"));
            } else {
                statisticsDTO.setTotalNumT(reader.getInteger("totalNum"));
                statisticsDTO.setTotalAmountT(reader.getBigDecimal("totalAmount"));
            }
        }


        //今日，昨日待支付(包含支付超时)数据
        String sqlToPay = "select ifnull(count(1), 0) as totalNum, ifnull(sum(sub.final_amount), 0) as totalAmount, DATE(sub.create_time) as createTime " +
                "from zb_sub_order sub" +
                " LEFT JOIN zb_order o on o.id=sub.rel_order_id " +
                " where sub.create_time >= '" + beginTime + "' " +
                "  and sub.create_time <= '" + endTime + "' " + sqlWhere +
                " and o.pay_status in (0,2) group by DATE(sub.create_time) ";

        List<SqlDataReader> readersToPay = executeReaderList(sqlToPay);
        for (SqlDataReader reader : readersToPay) {
            if (beginTime.contains(reader.getString("createTime"))) {
                //昨日的
                statisticsDTO.setTotalAmountToPayY(reader.getBigDecimal("totalAmount"));
                statisticsDTO.setTotalNumToPayY(reader.getInteger("totalNum"));
            } else {
                statisticsDTO.setTotalNumToPayT(reader.getInteger("totalNum"));
                statisticsDTO.setTotalAmountToPayT(reader.getBigDecimal("totalAmount"));
            }
        }

        //得到待发货订单数量
        statisticsDTO.setToDeliverNum(toDeliverNum(userDetails));
        statisticsDTO.setAfterSaleNum(afterSaleNum(userDetails));

        return statisticsDTO;
    }

    @Override
    public UserOrderCountDTO userOrderCount(Long userId) {
        UserOrderCountDTO userOrderCountDTO = new UserOrderCountDTO();

        //待收货数量
        String sql = "select distinct a.id  " +
                "from zb_sub_order a " +
                "         left join zb_order b on a.rel_order_id = b.id " +
                "         left join zb_order_item c on a.id = c.sub_order_id " +
                "where b.pay_status = 1 and b.rel_user_id=" + userId +
                "  and c.deliver_status = 0 and a.order_status=0 ";
        EntityManager em = emf.createEntityManager();
        Query countQuery = em.createNativeQuery(buildCountSql(sql));
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        userOrderCountDTO.setToDeliverNum(count.intValue());

        //待付款数量
        sql = "select count(1) from zb_order where pay_status=0 and rel_user_id=" + userId;
        countQuery = em.createNativeQuery(sql);
        count = (BigInteger) countQuery.getSingleResult();
        userOrderCountDTO.setToPayNum(count.intValue());

        //待签收
        sql = "select distinct a.id  " +
                "from zb_sub_order a " +
                "         left join zb_order b on a.rel_order_id = b.id " +
                "         left join zb_order_item c on a.id = c.sub_order_id " +
                "where b.pay_status = 1 and b.rel_user_id=" + userId +
                "  and c.deliver_status = 1 and a.order_status=0 ";

        countQuery = em.createNativeQuery(buildCountSql(sql));
        count = (BigInteger) countQuery.getSingleResult();
        userOrderCountDTO.setToReceiveNum(count.intValue());

        //售后数量
        sql = "SELECT COUNT(1) FROM zb_after_sale_bill where after_sale_status not in (0,4,5) and rel_user_id=" + userId;
        countQuery = em.createNativeQuery(sql);
        count = (BigInteger) countQuery.getSingleResult();
        userOrderCountDTO.setAfterSaleNum(count.intValue());
        em.close();
        return userOrderCountDTO;
    }

    @Override
    public int toDeliverNum(UserDetails userDetails) {
        String sql = "select distinct a.id  " +
                "from zb_sub_order a " +
                "         left join zb_order b on a.rel_order_id = b.id " +
                "         left join zb_order_item c on a.id = c.sub_order_id " +
                "where b.pay_status = 1 " +
                "  and c.deliver_status = 0 and a.order_status=0 ";

        if (userDetails.getRoleType() == RoleEnum.MERCHANT) {
            sql += "  and a.rel_merchant_id =" + userDetails.getId();
        }

        return getCount(buildCountSql(sql)).intValue();
    }

    @Override
    public int afterSaleNum(UserDetails userDetails) {
        String sql = "SELECT COUNT(1) FROM zb_after_sale_bill where after_sale_status not in (0,4,5)";
        if (userDetails.getRoleType() == RoleEnum.MERCHANT) {
            sql += " and rel_merchant_id=" + userDetails.getId();
        }

        return getCount(sql).intValue();
    }

    @Override
    public int yesterdayUv(UserDetails userDetails) {
        return 0;
    }

    @Override
    public void statistics(LocalDate date) {
        if (orderStatisticsRepository.existsByDate(date)) {
            return;
        }
        OrderStatisticsPO po = new OrderStatisticsPO();
        po.setDate(date);
        //昨日的
        LocalDateTime start = date.atStartOfDay();
        LocalDateTime end = Jsr310Utils.Date.endOfDay(date);

        String sqlWhere = " and a.create_time >= '" + Jsr310Utils.DateTime.asString(start) + "' and a.create_time <='" + Jsr310Utils.DateTime.asString(end) + "'";

        String sql = "select ifnull(count(1),0) as num, b.order_type " +
                "from zb_order a " +
                "left join zb_sub_order b on a.id = b.rel_order_id " +
                "where a.pay_status = 1 " + sqlWhere +
                "group by b.order_type";

        List<SqlDataReader> readers = executeReaderList(sql);

        int payedCount = 0;
        int normalCount = 0;

        for (SqlDataReader reader : readers) {
            int orderType = reader.getInteger("order_type");
            int num = reader.getInteger("num");
            if (orderType == OrderEnums.OrderType.NORMAL.getCode()) {
                normalCount += num;
            }
            payedCount += num;
        }
        po.setPayedCount(payedCount);
        po.setNormalCount(normalCount);

        //未支付
        sql = "select ifnull(count(1),0) as num from zb_order a where pay_status!=1" + sqlWhere;
        int unpayCount = executeReader(sql).getInteger("num");
        po.setUnpayCount(unpayCount);

        //直播订单
        sql = "select ifnull(count(1),0) as num, b.order_type " +
                "from zb_order a " +
                "left join zb_sub_order b on a.id = b.rel_order_id " +
                "where a.pay_status = 1 and b.live_session_id is not null " + sqlWhere +
                "group by b.order_type";
        readers = executeReaderList(sql);
        int normalLiveCount = 0;
        int auctionLiveCount = 0;
        for (SqlDataReader reader : readers) {
            int orderType = reader.getInteger("order_type");
            int num = reader.getInteger("num");
            if (orderType == OrderEnums.OrderType.NORMAL.getCode()) {
                normalLiveCount += num;
            }
            if (orderType == OrderEnums.OrderType.AUCTION.getCode()) {
                auctionLiveCount += num;
            }
        }
        po.setNormalLiveCount(normalLiveCount);
        po.setAuctionLiveCount(auctionLiveCount);

        //售后完成的订单
        // TODO: 2020/10/10 用新的订单生命状态

        save(po);
    }

    @Override
    public List<OrderStatisticsPO> sevenDays() {
        LocalDate end = LocalDate.now().minusDays(1);
        LocalDate begin = end.minusDays(7);

        return orderStatisticsRepository.findByDateIsBetween(begin, end);
    }

    @Override
    public Page<OrderStatisticsPO> findAll(int pageIndex, int pageSize, String begin, String end) {
        Specification<OrderStatisticsPO> specification = ((root, cq, cb) -> {
            List<Predicate> predicates = new ArrayList<>();

            if (StringUtils.isNotEmpty(begin)) {
                LocalDate beginDate = Jsr310Utils.DateTime.of(begin).toLocalDate();
                predicates.add(cb.greaterThanOrEqualTo(root.get(OrderStatisticsPO_.date), beginDate));
            }
            if (StringUtils.isNotEmpty(end)) {
                LocalDate endDate = Jsr310Utils.DateTime.of(end).toLocalDate();
                predicates.add(cb.lessThanOrEqualTo(root.get(OrderStatisticsPO_.date), endDate));
            }

            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        });

        return orderStatisticsRepository.findAll(specification, PageRequest.of(pageIndex - 1, pageSize, Sort.Direction.DESC, "date"));
    }

    @Override
    protected SpecificationAndSort<OrderStatisticsPO> specificationAndSort(BaseSearchDTO searchDTO) {
        return null;
    }

    @Override
    public OrderStatisticsPO save(BaseSaveDTO saveDTO) {
        return null;
    }

    @Override
    public Page<OrderStatisticsPO> findAll(int pageIndex, int pageSize) {
        return orderStatisticsRepository.findAll(PageRequest.of(pageIndex - 1, pageSize, Sort.Direction.DESC, "date"));
    }
}
